package cn.qdu.dao;

import cn.qdu.model.FriendRequest;
import cn.qdu.model.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FriendDao {
    // 发送好友请求
    public boolean sendFriendRequest(String userId, String friendId) throws SQLException {
        String sql = "INSERT INTO friendships(user_id, friend_id, status) VALUES(?, ?, 'pending')";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, friendId);

            return stmt.executeUpdate() > 0;
        }
    }

    // 接受好友请求
    public boolean acceptFriendRequest(String userId, String friendId) throws SQLException {
        String sql = "UPDATE friendships SET status = 'accepted' WHERE user_id = ? AND friend_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, friendId);
            stmt.setString(2, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    // 拒绝好友请求
    public boolean rejectFriendRequest(String userId, String friendId) throws SQLException {
        String sql = "UPDATE friendships SET status = 'rejected' WHERE user_id = ? AND friend_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, friendId);
            stmt.setString(2, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    // 删除好友关系
    public boolean removeFriend(String userId, String friendId) throws SQLException {
        String sql = "DELETE FROM friendships WHERE " +
                "(user_id = ? AND friend_id = ?) OR " +
                "(user_id = ? AND friend_id = ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, friendId);
            stmt.setString(3, friendId);
            stmt.setString(4, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    // 获取好友请求列表
    public List<FriendRequest> getFriendRequests(String userId) throws SQLException {
        List<FriendRequest> requests = new ArrayList<>();
        String sql = "SELECT f.*, u.nickname, u.avatar FROM friendships f " +
                "JOIN users u ON f.user_id = u.user_id " +
                "WHERE f.friend_id = ? AND f.status = 'pending'";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    FriendRequest request = new FriendRequest();
                    request.setFriendshipId(rs.getInt("friendship_id"));
                    request.setUserId(rs.getString("user_id"));
                    request.setFriendId(rs.getString("friend_id"));
                    request.setStatus(rs.getString("status"));
                    request.setCreatedAt(rs.getTimestamp("created_at"));

                    User user = new User();
                    user.setUserId(rs.getString("user_id"));
                    user.setNickname(rs.getString("nickname"));
                    user.setAvatar(rs.getString("avatar"));
                    request.setUser(user);

                    requests.add(request);
                }
            }
        }
        return requests;
    }

    // 获取好友列表 (修复逻辑)
    public List<User> getFriends(String userId) throws SQLException {
        List<User> friends = new ArrayList<>();
        // 修复SQL：正确查询双向好友关系
        String sql = "SELECT u.* FROM friendships f " +
                "JOIN users u ON " +
                "  (f.user_id = u.user_id AND f.friend_id = ?) OR " +
                "  (f.friend_id = u.user_id AND f.user_id = ?) " +
                "WHERE f.status = 'accepted' AND u.user_id != ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, userId);
            stmt.setString(3, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getString("user_id"));
                    user.setNickname(rs.getString("nickname"));
                    user.setAvatar(rs.getString("avatar"));
                    user.setGender(rs.getString("gender"));
                    user.setBirthDate(rs.getDate("birth_date"));
                    friends.add(user);
                }
            }
        }
        return friends;
    }

    // 检查是否是好友 (修复逻辑)
    public boolean isFriend(String userId, String friendId) throws SQLException {
        String sql = "SELECT 1 FROM friendships WHERE " +
                "((user_id = ? AND friend_id = ?) OR " +
                "(user_id = ? AND friend_id = ?)) AND status = 'accepted'";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, friendId);
            stmt.setString(3, friendId);
            stmt.setString(4, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    // 检查是否有待处理的好友请求
    public boolean hasPendingRequest(String userId, String friendId) throws SQLException {
        String sql = "SELECT 1 FROM friendships WHERE " +
                "(user_id = ? AND friend_id = ? AND status = 'pending') OR " +
                "(user_id = ? AND friend_id = ? AND status = 'pending')";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, friendId);
            stmt.setString(3, friendId);
            stmt.setString(4, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    // 获取推荐好友(基于共同好友) - 优化SQL
    public List<User> getRecommendedFriends(String userId) throws SQLException {
        List<User> recommendedFriends = new ArrayList<>();
        String sql = "SELECT DISTINCT u.*, " +
                "(SELECT COUNT(*) FROM friendships f1 " +
                " WHERE (f1.user_id = u.user_id OR f1.friend_id = u.user_id) " +
                " AND (f1.user_id IN (SELECT user_id FROM friendships WHERE friend_id = ? AND status='accepted') " +
                "      OR f1.friend_id IN (SELECT user_id FROM friendships WHERE friend_id = ? AND status='accepted'))) AS mutual_friends " +
                "FROM users u " +
                "WHERE u.user_id != ? " +
                "AND NOT EXISTS ( " +
                "    SELECT 1 FROM friendships f2 " +
                "    WHERE (f2.user_id = ? AND f2.friend_id = u.user_id) " +
                "       OR (f2.friend_id = ? AND f2.user_id = u.user_id)" +
                ") " +
                "ORDER BY mutual_friends DESC " +
                "LIMIT 5";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            stmt.setString(2, userId);
            stmt.setString(3, userId);
            stmt.setString(4, userId);
            stmt.setString(5, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getString("user_id"));
                    user.setNickname(rs.getString("nickname"));
                    user.setAvatar(rs.getString("avatar"));
                    user.setFriendCount(rs.getInt("mutual_friends"));
                    recommendedFriends.add(user);
                }
            }
        }
        return recommendedFriends;
    }

    // 获取共同好友数
    public int getMutualFriendsCount(String userId1, String userId2) throws SQLException {
        String sql = "SELECT COUNT(*) AS mutual_count FROM friendships f1 " +
                "JOIN friendships f2 ON " +
                "  (f1.user_id = f2.user_id OR f1.user_id = f2.friend_id) " +
                "WHERE f1.status = 'accepted' AND f2.status = 'accepted' " +
                "AND ((f1.user_id = ? AND f2.friend_id = ?) OR (f1.friend_id = ? AND f2.user_id = ?))";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId1);
            stmt.setString(2, userId2);
            stmt.setString(3, userId1);
            stmt.setString(4, userId2);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("mutual_count");
                }
            }
        }
        return 0;
    }
}